package com.code;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.Global.Global;
import com.example.R;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.TextView;

public class TK_TongHop extends Activity {
	private String strKhoChon = "";
	ArrayList<Integer> date_actual;
	TextView tvTonQuyDau, tvThu, tvChi, tvTonCuoi, tvTonNH, tvNoPhaiTra,
			tvNoPhaiThu, tvDoanhSo,btnTroLaiTong;
	private String date;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.tk_tong);

		strKhoChon = super.getIntent().getExtras().getString("strKhoChon");
		date_actual = super.getIntent().getExtras()
				.getIntegerArrayList("date_actual");
		date = String.valueOf(date_actual.get(2))
				+ DetailCuocGoi.setMonth(date_actual.get(1))
				+ Global.formatDay(date_actual.get(0));
		// String date =
		// String.valueOf(date_actual.get(2))+DetailCuocGoi.setMonth(date_actual.get(1))+String.valueOf(date_actual.get(0));

		tvTonQuyDau = (TextView) findViewById(R.id.tvTonQuyDau);
		tvThu = (TextView) findViewById(R.id.tvThu);
		tvChi = (TextView) findViewById(R.id.tvChi);
		tvTonCuoi = (TextView) findViewById(R.id.tvTonCuoi);
		tvTonNH = (TextView) findViewById(R.id.tvTonNH);
		tvNoPhaiTra = (TextView) findViewById(R.id.tvNoPhaiTra);
		tvNoPhaiThu = (TextView) findViewById(R.id.tvNoPhaiThu);
		tvDoanhSo = (TextView) findViewById(R.id.tvDanhSo);
		btnTroLaiTong = (TextView)findViewById(R.id.btnTroLaiTong);

		tvTonQuyDau.setText(Global.formatCurrency(getTonQuyDau()));
		tvThu.setText(Global.formatCurrency(getThu()));
		tvChi.setText(Global.formatCurrency(getChi()));
		tvTonCuoi.setText(Global.formatCurrency(getTonCuoi()));
		tvTonNH.setText("0");
		tvNoPhaiTra.setText(Global.formatCurrency(noPhaiTraAndThu(false)));
		tvNoPhaiThu.setText(Global.formatCurrency(noPhaiTraAndThu(true)));
		tvDoanhSo.setText(Global.formatCurrency(getDoanhThu()));
		btnTroLaiTong.setOnClickListener(new OnClickListener() {
			public void onClick(View v) {
				setResult(RESULT_CANCELED);
				finish();
			}
		});
	}

	public String getTonQuyDau() {
		String ton = "0";
 		String dateToQuyDau = String.valueOf(date_actual.get(2))
				+ DetailCuocGoi.setMonth(date_actual.get(1))
				+ Global.formatDay(date_actual.get(0));
		dateToQuyDau = getDate(-1, dateToQuyDau).toString();
		String sql = " SELECT [dbo].[fn_TonQuyByKhoVND]('" + strKhoChon + "','"
				+ dateToQuyDau + "')";
		try {
			ResultSet rs = Global.excuteQuerySQLServer(sql);
			if (rs.next())
				ton = rs.getString(1);
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ton;
	}

	public String getThu() {
		String thu = "0";
		StringBuffer sql = new StringBuffer(
				" SELECT CASE WHEN (ISNULL(SUM(SoTien),'') = '' ) then 0 else SUM(SoTien) END ");
		sql.append(" FROM Phieuthu ");
		sql.append(" WHERE ngay >= '" + date + "' AND ngay <= '" + date + "'");
		if (!strKhoChon.equals(""))
			sql.append(" AND Kho COLLATE DATABASE_DEFAULT in (select value from  dbo.fn_Split('"+strKhoChon+"', ','))");
		try {
			ResultSet rs = Global.excuteQuerySQLServer(sql.toString());
			if (rs.next())
				thu = rs.getString(1);
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return thu;
	}

	public String getChi() {
		String chi = "0";
		StringBuffer sql = new StringBuffer(
				" SELECT CASE WHEN (ISNULL(SUM(SoTien),'') = '' ) then 0 else SUM(SoTien) END ");
		sql.append(" FROM Phieuchi ");
		sql.append(" WHERE ngay >= '" + date + "' AND ngay <= '" + date + "'");
		if (!strKhoChon.equals(""))
			sql.append( " AND Kho COLLATE DATABASE_DEFAULT in (select value from  dbo.fn_Split('"+strKhoChon+"', ','))");
		try {
			ResultSet rs = Global.excuteQuerySQLServer(sql.toString());
			if (rs.next())
				chi = rs.getString(1);
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return chi;
	}

	public String getTonCuoi() {
		String ton = "0";
		String sql = " SELECT [dbo].[fn_TonQuyByKhoVND]('" + strKhoChon + "','"
				+ date + "')";
		try {
			ResultSet rs = Global.excuteQuerySQLServer(sql);
			if (rs.next())
				ton = rs.getString(1);
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ton;
	}
	
	public String noPhaiTraAndThu(Boolean isThu) {
		String thuChi = "0";
		String sqlCreate = " Create Table TempCongNoTongHop (Col1 nvarchar(14), Col2 nvarchar(100),col3 nvarchar(100),col4 float,col5 float,"
				+ " col6 float,col7 float,col8 float,col9 float,phaitrack float,phaithuck float,ngaythugm datetime) "
				+ " INSERT INTO TempCongNoTongHop EXEC [dbo].[spCongNoTongHop] '"
				+ date + "','" + date + "','' ";
		Global.excuteUpdate(sqlCreate);		
		String sqlSum = " SELECT SUM(phaitrack),SUM(phaithuck) FROM TempCongNoTongHop ";
		try {
			ResultSet rs = Global.excuteQuerySQLServer(sqlSum);
			if (rs.next()) {
				if (isThu)
					thuChi = rs.getString(2);
				else
					thuChi = rs.getString(1);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String sqlDrop = " drop TABLE TempCongNoTongHop ";
		Global.excuteUpdate(sqlDrop);
		return thuChi;
	}

	public String getDoanhThu() {
		String doanhThu = "0";
		StringBuffer sql = new StringBuffer(" SELECT CASE WHEN ISNULL(SUM((vattuxuat.SL * vattuxuat.dongia) * (1 + ISNULL(vattuxuat.Thue, 0))),'') = ''");
		sql.append(" THEN 0 ELSE SUM((vattuxuat.SL * vattuxuat.dongia) * (1 + ISNULL(vattuxuat.Thue, 0))) END AS DoanhSo ");
		 sql.append(" FROM Phieuxuat px INNER JOIN vattuxuat ON px.SoCt = vattuxuat.SoCT "); 
		 sql.append(" INNER JOIN Products ON vattuxuat.[Product ID] = Products.[Product ID]");
		 sql.append(" AND px.Ngay <= '"+date+"' "); 
		 sql.append(" AND px.Ngay >= '"+date+"'");
		 if(!strKhoChon.equals(""))
			 sql.append(" AND px.MSK COLLATE DATABASE_DEFAULT in (select value from  dbo.fn_Split('"+strKhoChon+"', ','))");
//		 sql.append(" GROUP BY px.[Supplier ID], Products.TheoDoiVo) a ");
		 try {
				ResultSet rs = Global.excuteQuerySQLServer(sql.toString());
				if (rs.next()) {				
					doanhThu = rs.getString(1);
				}
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		return doanhThu;
	}
	
	public static Timestamp getDate(Integer value, String date) {
		Timestamp t = null;
		String sql = " SELECT DATEADD(DD," + value + ",'" + date + "')";
		ResultSet rs = Global.excuteQuerySQLServer(sql);
		try {
			if (rs.next())
				t = rs.getTimestamp(1);
			else
				t = null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return t;
	}
}
